#!/usr/bin/perl -w
use strict;
use Getopt::Long;
use DBI;
use DBD::Pg;
use Digest::MD5 qw(md5_hex);
use Net::FTP;
use LWP::Simple;
use HTTP::Date;
use Carp;

#88888888888888888888888888888888888888888888888888888888888888888888888888888888

#	Usage

#88888888888888888888888888888888888888888888888888888888888888888888888888888888

my $usage = <<'USAGE';

USAGE:

    panda_master_update.pl  dbname
                            [--verbose]
                            [--logfile FILE        the name of the log file. ]
                            [--user STRING         the postgreSQL user name  ]
                            [--host STRING         the postgreSQL host name  ]
                            [--password STRING     the postgreSQL password   ]
                            [--interactive         console messages printed  ]

RESULTS:


   This script goes through all the databases registered with panda and updates
   the sequences and accessions

USAGE


#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#	Get options

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888
# mandatory
my $dbname 		= "";

# optional parameters
my $password		= "";
my $interactive 	= 0;
my $verbose			= 0;
my $help;

# override overall panda settings for this session
my $user			= "";
my $logfile			= "";
my $host			= "";

# globals
# as above but not user specifiable
my $working_dir		= "";
my $panda_cmd_line	= "";

GetOptions(
			'user=s'			=> \$user,
			'password=s'		=> \$password,
			'logfile=s'			=> \$logfile,
			'verbose'			=> \$verbose,
			'interactive'		=> \$interactive,
			'help'				=> \$help);

$dbname = defined $ARGV[0] ? $ARGV[0] : "";
die $usage if ($help);
die ($interactive ? "\nERROR:\n\tMissing Argument: dbname\n$dbname\n$usage" : "")
				unless ($dbname);
				


#88888888888888888888888888888888888888888888888888888888888888888888888888888888

#	Various functions

#88888888888888888888888888888888888888888888888888888888888888888888888888888888

				
#_________________________________________________________________________________________
#                                                                                        #
#	Declarations																		 #
sub ftp_get_file_if_newer($$$);                                                          #
sub retrieve_panda_parameters($\%);                                                      #
sub substitute_params(\%\%);                                                             #
sub parse_source_dependencies(\%\%);                                                     #
sub set_database_sources_string($$$$$);                                                  #
sub get_database_sources_date($$$);                                                      #
sub set_database_sources_date($$$$$);                                                    #
sub die_source_database($$$$$);                                                          #
sub retrieve_source_parameters($\%);                                                     #
sub debug_printout_parameters($);                                                        #
sub update($$$$$$$);                                                                     #
sub print_log($$);                                                                       #
#_________________________________________________________________________________________


sub print_log($$)
{
	my ($logfile, $err_message) = @_;
	unless (open LOGFILE, ">>$logfile")
	{
		if ($logfile ne '')
		{
			print STDERR "Error:\n\tCould not open log file [$logfile].\n";
		}
		die ($interactive ? "$err_message\n" : "\n");
	}
	print LOGFILE "$err_message\n";
}

#_________________________________________________________________________________________

#	download_file

#		Input:

#			$sources				reference to hash of parameters for a source
#			$dbh					database handle

#_________________________________________________________________________________________
sub	download_file($$)
{
	my ($source, $dbh) = @_;


	#
	#	if some file has already been downloaded, delete if it has been corrupted
	#								(i.e. different MD5)
	#
	if (-f $source->{'local_file'})
	{
		# get MD5
		if (!open LOCAL_FILE, $source->{'local_file'})
		{
			return die_source_database($dbh,
										"open ".$source->{'local_file'},
										$source->{'name'},
										'Invalid local file permissions',
										undef);
		}
		my $ctx = Digest::MD5->new;
		$ctx->addfile(*LOCAL_FILE);
		my $digest = $ctx->hexdigest;
		close LOCAL_FILE;
		print "$digest / $source->{'local_file_MD5'}\n";
		if ($digest ne $source->{'local_file_MD5'})
		{
			if ($interactive && defined $source->{'local_file_MD5'} &&
				$source->{'local_file_MD5'} ne '')
			{
				print "Local file corrupt and will be removed\n";
			}
			unlink $source->{'local_file'}
		}
	}
	
		
	#
	#	download file
	#
	my $local_file_date = str2time($source->{'local_file_date'}, 'GMT');
	$local_file_date = undef unless (-f $source->{'local_file'});
	my $result = ftp_get_file_if_newer(	$source->{'remote_url'},
                                        $source->{'local_file'},
										$local_file_date);
	unless ($result =~/^\d+$/)
	{
		return die_source_database($dbh, $result, $source->{'name'}, 'Download failed', undef);
	}
	

	#
	#	success
	#
	if ($result == 1)		# file updated
	{
		print STDOUT "\tSuccessfully downloaded\n" if ($interactive);
		
		# get MD5
		if (!open LOCAL_FILE, $source->{'local_file'})
		{
			return die_source_database($dbh, "open ".$source->{'local_file'},
								$source->{'name'}, 'Invalid local file permissions',
								undef);
		}
		my $ctx = Digest::MD5->new;
		$ctx->addfile(*LOCAL_FILE);
		my $digest = $ctx->hexdigest;
		close LOCAL_FILE;
		set_database_sources_string($dbh, $source->{'name'},
									'local_file_MD5', $digest, undef);
		
		
		$local_file_date = time2str($local_file_date);
		set_database_sources_date($dbh, $source->{'name'},
									'local_file_date', $local_file_date, undef);
	}
	elsif ($result == 2)
	{
		print STDOUT "\tFile already up-to-date\n" if ($interactive);
	}
	
	return 1;
}



#_________________________________________________________________________________________

#	retrieve_panda_parameters


#		retrieve panda system parameters

#_________________________________________________________________________________________
sub retrieve_panda_parameters($\%)
{
	my ($dbh, $parameters) = @_;
	my @param_names =qw (	panda_cmd_line
							panda_scripts_dir
							db_scripts_dir
							panda_working_dir
							seq_dir
							panda_dbname
							panda_usr_name
							panda_remote_host
							logfile);
	my $sql_cmd = 'SELECT ' . join (', ', @param_names) . " FROM panda_parameters";
	if (!(@$parameters{@param_names} = $dbh->selectrow_array($sql_cmd)))
	{
		return die_source_database($dbh, 'retrieve panda parameters', undef, undef, $sql_cmd);
	}
	
	$dbname		||= $parameters->{'panda_dbname'};
	$user			||= $parameters->{'panda_usr_name'};
	$working_dir	||= $parameters->{'panda_working_dir'};
	$panda_cmd_line	||= $parameters->{'panda_cmd_line'};
	$working_dir	 .= '/' unless ($working_dir =~ m[/$]);
	$logfile		||= $parameters->{'logfile'};
	$host			||= $parameters->{'panda_remote_host'};

	$parameters->{'panda_dbname'}	   = $dbname        ;
	$parameters->{'panda_usr_name'}	   = $user           ;
	$parameters->{'panda_working_dir'} = $working_dir    ;
	$parameters->{'panda_cmd_line'}	   = $panda_cmd_line ;
	$parameters->{'logfile'}	       = $logfile        ;
	$parameters->{'panda_remote_host'} = $host           ;
}

#_________________________________________________________________________________________

#	substitute_params


#		use the panda system-wide parameters in place of any substitutable variables
#			in the database source parameters

#_________________________________________________________________________________________
sub substitute_params(\%\%)
{
	my ($parameters, $source_data) = @_;

	for my $source (keys %$source_data)
	{
		for my $source_param_key (keys %{$source_data->{$source}})
		{
			for (keys %$parameters)
			{
				my $substituter = '\$'.uc($_).'\$';
				my $substitutee = $parameters->{$_};
				if (defined $substituter && defined $substitutee &&
					defined $source_data->{$source}{$source_param_key})
				{
					$source_data->{$source}{$source_param_key} =~
													s/$substituter/$substitutee/g;
				}
			}
		}
	}

}

				

#_________________________________________________________________________________________

#	parse_source_dependencies


#		retrieve the list of sequence sources on which each sequence source is in turn
#			dependent

#_________________________________________________________________________________________
sub parse_source_dependencies(\%\%)
{
	my ($source_parameters, $source_dependencies) = @_;

	foreach my $source_name(keys %$source_parameters)
	{
		next unless $source_parameters->{$source_name}{'depends'};
		$source_dependencies->{$source_name} =
			[split /\W+/,  $source_parameters->{$source_name}{'depends'}];
	}
}

#_________________________________________________________________________________________

#	get_database_sources_date


#		get the date we last failed

#_________________________________________________________________________________________
my $recurse_guard = 0;
sub set_database_sources_string($$$$$)
{
	my ($dbh, $source_name, $field, $value, $no_error) = @_;
	my $sql_cmd =	"UPDATE database_sources ".
						"SET $field = ? ".
						"WHERE name = '$source_name'";
	unless ($dbh->do($sql_cmd, undef, $value))
	{
		return if ($no_error);
		return die_source_database($dbh, "update $field in panda to [$value]",
							$source_name, "$field update", $sql_cmd);
	}
}

sub get_database_sources_date($$$)
{
	my ($dbh, $source_name, $field) = @_;

	my $sql_cmd = "SELECT $field FROM database_sources WHERE name =?";
	my @failed_date = $dbh->selectrow_array($sql_cmd, undef, $source_name);
	if (@failed_date == 0)
	{
		return die_source_database($dbh, "retrieve any '$field' from database_sources",
							$source_name, "$field retrieval",
							$sql_cmd);
	}
	return pop @failed_date;
}

sub set_database_sources_date($$$$$)
{
	my ($dbh, $source_name, $field, $date, $no_format) = @_;
	my @param = ($date, $source_name);
    my $place_holder = '?';
	$place_holder = shift @param if $no_format;
	my $sql_cmd =	"UPDATE database_sources ".
						"SET $field = $place_holder ".
						"WHERE name = ?";
	unless ($dbh->do($sql_cmd, undef, @param))
	{
		return die_source_database($dbh, "update the timestamp field \"$field\" in panda",
								$source_name, "$field update", $sql_cmd);
	}
}

#_________________________________________________________________________________________

#	die_source_database($$$$$)


#		prints error, puts the fail reason into panda and dies

#_________________________________________________________________________________________
sub die_source_database($$$$$)
{
	my ($dbh, $err_description, $source_name, $status, $sql_cmd) = @_;
	my $err_message =	"\n".
						"Error!"		  					."\n\t".
							"Could not ". $err_description	."\n\t";
	if (defined $dbh && defined $dbh->errstr)
	{
		$err_message .=		$dbh->errstr					."\n\t";
	}
#	if (defined $!)
#	{
#		$err_message .=		$!								."\n\t";
#	}
	if (defined $sql_cmd)
	{
		$err_message .=		'['.$sql_cmd.']'				."\n\t";
	}
	$err_message .= "\n";

	
	# log error
	{
		unless (open LOGFILE, ">>$logfile")
		{
			if ($logfile ne '')
			{
				print STDERR "Error:\n\tCould not open log file [$logfile].\n";
			}
			die ($interactive ? "$err_message\n" : "\n");
		}
		print LOGFILE "$err_message\n";
		print STDOUT $err_message if ($interactive);
	}
	
	# save failed status to panda database
	if (defined $status && defined $dbh)
	{
		set_database_sources_string($dbh, $source_name, 'status', "Failed: $status", 1);
	}
	
	$dbh->disconnect if defined $dbh;
	exit 1;
	
#	croak $err_message;
#	exit 1;
#	$! = 1;
#	die "\n";
}

#_________________________________________________________________________________________

#	retrieve_source_parameters


#		retrieve a list of parameters for a particular source
#_________________________________________________________________________________________
sub retrieve_source_parameters($\%)
{
	my ($dbh, $parameters) = @_;
	my @param_names =	qw	(   name
								db_out_of_date
								description
								depends
								date_added
								remote_url
								db_last_update
								db_update_interval
								local_file
								local_file_date
								local_file_MD5
								check_up_to_date_cmds
								pre_process_cmds
								run_panda
								panda_param_fasta_file
								panda_param_regex
								panda_param_db_name
								panda_param_update_stats
								panda_param_misc
								post_process_cmds
								update_failed_date
								status);
	
	my @sql_param = @param_names;
	$sql_param[1] = 'not db_update_interval is null and '.
					'(db_last_update is null or '.
					'db_last_update - current_timestamp + db_update_interval < 0)';

	my $sql_cmd = 'SELECT ' . join (', ', @sql_param) . ' FROM database_sources';
	my $sth = $dbh->prepare($sql_cmd);
	unless ($sth->execute() && $sth->rows() )
	{
		$sth->finish();
		return die_source_database($dbh, "retrieve panda database source parameters",
							undef, undef, $sql_cmd);
	}
	
	while (my @results = $sth->fetchrow_array())
	{
		@{$parameters->{$results[0]}}{@param_names} = @results;
	}
}
				
				

#_________________________________________________________________________________________

#	ftp_get_file_if_newer($$$)

#		Input:

#			$remote_path_file		file to download
#			$destination_file		where to save downloaded file to
#			$local_date				file must be more recent than this

#_________________________________________________________________________________________
my %rc_constants =
(
(RC_NO_CONTENT                         +0)=> "RC_NO_CONTENT                    ",
(RC_RESET_CONTENT                      +0)=> "RC_RESET_CONTENT                 ",
(RC_PARTIAL_CONTENT                    +0)=> "RC_PARTIAL_CONTENT               ",
(RC_MULTIPLE_CHOICES                   +0)=> "RC_MULTIPLE_CHOICES              ",
(RC_MOVED_PERMANENTLY                  +0)=> "RC_MOVED_PERMANENTLY             ",
(RC_MOVED_TEMPORARILY                  +0)=> "RC_MOVED_TEMPORARILY             ",
(RC_BAD_REQUEST                        +0)=> "RC_BAD_REQUEST                   ",
(RC_UNAUTHORIZED                       +0)=> "RC_UNAUTHORIZED                  ",
(RC_PAYMENT_REQUIRED                   +0)=> "RC_PAYMENT_REQUIRED              ",
(RC_FORBIDDEN                          +0)=> "RC_FORBIDDEN                     ",
(RC_NOT_FOUND                          +0)=> "RC_NOT_FOUND                     ",
(RC_METHOD_NOT_ALLOWED                 +0)=> "RC_METHOD_NOT_ALLOWED            ",
(RC_NOT_ACCEPTABLE                     +0)=> "RC_NOT_ACCEPTABLE                ",
(RC_PROXY_AUTHENTICATION_REQUIRED      +0)=> "RC_PROXY_AUTHENTICATION_REQUIRED ",
(RC_REQUEST_TIMEOUT                    +0)=> "RC_REQUEST_TIMEOUT               ",
(RC_CONFLICT                           +0)=> "RC_CONFLICT                      ",
(RC_GONE                               +0)=> "RC_GONE                          ",
(RC_LENGTH_REQUIRED                    +0)=> "RC_LENGTH_REQUIRED               ",
(RC_PRECONDITION_FAILED                +0)=> "RC_PRECONDITION_FAILED           ",
(RC_REQUEST_ENTITY_TOO_LARGE           +0)=> "RC_REQUEST_ENTITY_TOO_LARGE      ",
(RC_REQUEST_URI_TOO_LARGE              +0)=> "RC_REQUEST_URI_TOO_LARGE         ",
(RC_UNSUPPORTED_MEDIA_TYPE             +0)=> "RC_UNSUPPORTED_MEDIA_TYPE        ",
(RC_INTERNAL_SERVER_ERROR              +0)=> "RC_INTERNAL_SERVER_ERROR         ",
(RC_NOT_IMPLEMENTED                    +0)=> "RC_NOT_IMPLEMENTED               ",
(RC_BAD_GATEWAY                        +0)=> "RC_BAD_GATEWAY                   ",
(RC_SERVICE_UNAVAILABLE                +0)=> "RC_SERVICE_UNAVAILABLE           ",
(RC_GATEWAY_TIMEOUT                    +0)=> "RC_GATEWAY_TIMEOUT               ",
(RC_HTTP_VERSION_NOT_SUPPORTED         +0)=> "RC_HTTP_VERSION_NOT_SUPPORTED    ");
sub ftp_get_file_if_newer($$$)
{
	# get remote file date
	my ($remote_path_file,
		$destination_file) = @_;
	my $local_date = \$_[2];



	my ($remote_size, $remote_date);
	(undef, $remote_size, $remote_date) = head("$remote_path_file");


	#
	#	check if need to download
	#
	# print STDERR  ">",scalar localtime($$local_date), "\n", scalar localtime($remote_date),"\n";
	return 2 if ($remote_date && $$local_date && $$local_date >= $remote_date);
	
	
	if ($interactive && $verbose && $$local_date)
	{
		print "\tLocal file is out of date:\n";
		print "\tLocal date = ".time2str($$local_date). "\n";
		print "\tRemote date = ".time2str($remote_date). "\n";
	}
	print "\t'$remote_path_file' to $destination_file via FTP\n" if $interactive && $verbose;
	
	#
	#	retrieve file
	#
	my $rc = getstore("$remote_path_file", $destination_file);


	if  ($rc != RC_OK)
	{
		return "download the file '$remote_path_file' to $destination_file.\n".
		((defined $rc_constants{$rc}) ? "Return code = $rc_constants{$rc}\n": "").
		"$!\n";
	}


	#
	#	check success
	#
	return "save the file '$remote_path_file' to $destination_file"
		unless -f ($destination_file);

	#
	#	check correct file size
	#
	my $local_size = (stat($destination_file))[7];
	if (!$local_size || ($remote_size && $local_size != $remote_size))
	{
		# unlink $destination_file;
		return "download a file [$remote_path_file] of the correct size\n".
				"(Remote size=$remote_size, Local size=$local_size)\n";
	}
	
	#
	#	success
	#
	$$local_date = $remote_date;
	
	return 1;	
}



=pod
sub ftp_get_file_if_newer($$$)
{
	# get remote file date
	my ($remote_path_file,
		$destination_file) = @_;
	my $local_date = \$_[2];

	(my $host		 = $remote_path_file) =~ s[([^/]+).*][$1];


	my $ftp = Net::FTP->new($host, Debug =>0);
	$ftp or return "connect to $host";
	$ftp->login();
	$ftp->binary();
	#$ftp->pasv();
	
	
	#
	#	parse remote path and get file details
	#
	(my $remote_file = $remote_path_file) =~ s[.*/][];
	(my $remote_path = $remote_path_file) =~ s[[^/]+(.*)/.*][$1];;
	$ftp->cwd($remote_path) or return "change to the directory '$host$remote_path' to download '$remote_file'";
	my $remote_date = $ftp->mdtm($remote_file);
	my $remote_size = $ftp->size($remote_file);

	#
	#	check if need to download
	#
	# print STDERR  ">",scalar localtime($$local_date), "\n", scalar localtime($remote_date),"\n";
	return 2 if ($remote_date && $$local_date && $$local_date >= $remote_date);
	
	
	if ($interactive && $verbose && $$local_date)
	{
		print "\tLocal file is out of date:\n";
		print "\tLocal date = ".time2str($$local_date). "\n";
		print "\tRemote date = ".time2str($remote_date). "\n";
	}
	print "\t'$remote_path_file' to $destination_file via FTP\n" if $interactive && $verbose;
	
	#
	#	retrieve file
	#
	my $rc = $ftp->get($remote_file, $destination_file);
	return "download the file '$remote_path_file' to $destination_file.\n$!"
		unless $rc;


	#
	#	check success
	#
	return "save the file '$remote_path_file' to $destination_file"
		unless -f ($destination_file);

	#
	#	check correct file size
	#
	my $local_size = (stat($destination_file))[7];
	if (!$local_size || ($remote_size && $local_size != $remote_size))
	{
		unlink $destination_file;
		return "download a file [$remote_path_file] of the correct size";
	}
	
	#
	#	success
	#
	$$local_date = $remote_date;
	
	return 1;	
}


=cut
				

				
#_________________________________________________________________________________________

#	debug_printout_parameters($)

#		print all panda parameters; used for debugging
#_________________________________________________________________________________________
sub debug_printout_parameters($)
{
	my ($dbh) = @_;
	
	#
	# retrieve database sources and their dependencies
	#
	my %sources;
	retrieve_source_parameters($dbh, %sources);
	
	my %source_depends;
	parse_source_dependencies(%sources, %source_depends);
	
	
	#
	#	get global parameters and substitute variables / paths which are not hard-wired
	#
	my %parameters;
	retrieve_panda_parameters($dbh, %parameters);
	
	
	#
	#	run macro substitions from panda database
	#
	substitute_params(%parameters, %sources);
		
		
	for my $source_name (keys %sources)
	{
		print STDERR "\nDatabase: $source_name\n";
		print STDERR "Depends:\n", join "\n", @{$source_depends{$source_name}}, "" if $source_depends{$source_name};
		for (keys %{$sources{$source_name}})
		{
			printf STDERR "%-30s= %s\n", $_,
			(defined ($sources{$source_name}{$_}) ? $sources{$source_name}{$_}: "null");
		}
	}
	
	
}


				
				





#_________________________________________________________________________________________

#	update

#		Input:

#			$dbh					database handle
#			$curr_source			name of current database source to be updated
#			$sources				a hash of all sources
#			$updated_sources		a hash of all sources already updated
#           $source_depends			a hash of which database sources depend on which others
#			$parameters				panda system wide parameters
#			$recursion_level		prevent infinite recursion

#_________________________________________________________________________________________
sub update($$$$$$$)
{
	my ($dbh, $curr_source_name, $sources, $updated_sources, $source_depends, $parameters,
		   $recursion_level) = @_;

	#
	#	do not download too frequently
	#
	my $source = $sources->{$curr_source_name};
	return if ($source->{'db_out_of_date'} eq 'f');
	
	print STDOUT "Updating $curr_source_name\n" if ($interactive && $verbose);
	print_log($logfile, "Updating $curr_source_name\n") if ($verbose && $logfile);

	
	#_____________________________________________________________________________________
	#	recursion
	#_____________________________________________________________________________________
	
	
	#
	#	prevent infinite recursions for malformed / circular dependencies
	#
	++$recursion_level;
	if ($recursion_level > 2 * keys %$sources)
	{
		return die_source_database($dbh, "resolve cyclical database source dependencies",
							$curr_source_name, 'Cyclical dependencies', undef);
	}
	
	#
	#	update all sources on which this is dependent
	#
	foreach my $depend_name (@{$source_depends->{$curr_source_name}})
	{
		#	update 'parent' source if it exists
		
		if (!exists $sources->{$depend_name})
		{
			warn "Error!\n\t".
				"The source database '$depend_name' on which '$curr_source_name' depends does ".
				 "not exist. Please check your spelling!!\nList of sources:\n".
				join("=", keys %$sources), "\n\n";
		}
		
		elsif (!exists $updated_sources->{$depend_name})
		{
			update($dbh, $depend_name, $sources, $updated_sources, $source_depends, $parameters,
				   $recursion_level);
		}
	}
	--$recursion_level;
	#_____________________________________________________________________________________
	

	

	#
	#	presume this process will fail: write current date as failed date after
	#		saving previous date. Just in case we succeed!!
	#
	my $prev_update_failed_date =
		get_database_sources_date($dbh, $curr_source_name, 'update_failed_date');
	set_database_sources_date($dbh, $curr_source_name, 'update_failed_date',
								"date_trunc('second', CURRENT_TIMESTAMP)", 1);



	
	#
	#	download file if necessary
	#
	print STDOUT "\tDownloading ...\n" if ($interactive && $verbose);
	print_log($logfile, "\tDownloading ...\n") if ($verbose && $logfile);
	download_file($source, $dbh)
		if ($source->{'remote_url'});
	
		
	#
	#	run pre-process commands
	#
	if ($source->{'pre_process_cmds'})
	{
		print STDOUT "\tPre-processing ...\n" if ($interactive && $verbose);
		print_log($logfile, "\tPre-processing ...\n") if ($verbose && $logfile);
		my $cmd_text = $source->{'pre_process_cmds'};
#		$cmd_text .= " 2> $logfile" if $logfile;
		if (system ($cmd_text) != 0)
		{
			return die_source_database($dbh, "execute the pre-processing commands",
								$curr_source_name, 'Pre-process', $source->{'pre_process_cmds'});
		}
	}

	#
	#	run panda
	#
	if ($source->{'run_panda'})
	{
		print STDOUT "\tRunning Panda ...\n" if ($interactive && $verbose);
		print_log($logfile, "\tRunning Panda ...\n") if ($verbose && $logfile);
		my $cmd_text = 						$working_dir;
		$cmd_text .= 						$panda_cmd_line;
		$cmd_text .= " --err_log $logfile" if $logfile;
		if ($source->{'panda_param_regex'})
		{
			my $regex = $source->{'panda_param_regex'};
			#escape shell symbols
			$regex =~ s/([\\\|>])/\\$1/;
			$cmd_text .= " --regex '".			$source->{'panda_param_regex'} ."'";
		}
        $cmd_text .= " --name_source ".		$source->{'panda_param_db_name'};
		$cmd_text .= " --stats ".			$source->{'panda_param_update_stats'}
						if ($source->{'panda_param_update_stats'});
		
		# login text
		$cmd_text .= ' --dbname '.				$dbname;
		$cmd_text .= " --user ".				$user	if ($user);
		$cmd_text .= " --host ".				$host	if ($host);
		$cmd_text .= " --password ".			$source->{'panda_usr_passwd'}
						if ($source->{'panda_usr_passwd'});
		
											# e.g. --indices --orphans
		$cmd_text .= " ".					$source->{'panda_param_misc'};
		$cmd_text .= " --fasta_file ".		$source->{'panda_param_fasta_file'};

		
		if (system ($cmd_text) != 0)
		{
			return die_source_database($dbh, "running panda",
								$curr_source_name, 'running panda', $cmd_text);
		}
		
	}

	#
	#	run post-process commands
	#
	if ($source->{'post_process_cmds'})
	{
		print STDOUT "\tPost-processing ...\n" if ($interactive && $verbose);
		print_log($logfile, "\tPost-processing ...\n") if ($verbose && $logfile);
		my $cmd_text = $source->{'post_process_cmds'};
		$cmd_text .= "1&2> $logfile" if $logfile;
		if (system ($cmd_text) != 0)
		{
			return die_source_database($dbh, "execute the postprocessing commands",
								$curr_source_name, 'post-process', $source->{'post_process_cmds'});
		}
	}


	#
	#	Success: set updated flag
	#			remove failed date
	#
	print STDOUT "\tUpdated $curr_source_name\n" if ($interactive && $verbose);
	print_log($logfile, "\tUpdated $curr_source_name ...\n") if ($verbose && $logfile);
	$updated_sources->{$curr_source_name} = 1;
	set_database_sources_date($dbh, $curr_source_name, 'update_failed_date', $prev_update_failed_date, 0);
	set_database_sources_date($dbh, $curr_source_name, 'db_last_update',
								"date_trunc('second', CURRENT_TIMESTAMP)", 1);
	set_database_sources_string($dbh, $curr_source_name, 'status', "OK", undef);
}
				



#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#	Core logic!!

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888
#
# connect to database
#
my $dbihost = ($host) ? ";host=$host" : "";
my $dbh = DBI->connect( "dbi:Pg:dbname=$dbname$dbihost",
                        $user,
                        $password,
						{
                          RaiseError => 0,
                          AutoCommit => 1,
						  PrintError => 0
                        }
                      ) or
		die_source_database(undef, "connect to the database\n\t$DBI::errstr",
							undef, undef, undef);


#
# retrieve database sources and their dependencies
#
my %sources;
retrieve_source_parameters($dbh, %sources);

my %source_depends;
parse_source_dependencies(%sources, %source_depends);


#
#	get global parameters and substitute variables / paths which are not hard-wired
#
my %parameters;
retrieve_panda_parameters($dbh, %parameters);


#
#	run macro substitions from panda database
#
substitute_params(%parameters, %sources);



#
# update all database sources following their dependencies
#
my $recursion_level = 0;
my %updated_sources;
foreach my $curr_source_name (keys (%sources))
{
	if (!exists $updated_sources{$curr_source_name})
	{
		update($dbh, $curr_source_name, \%sources, \%updated_sources, \%source_depends, \%parameters,
			   $recursion_level);
	}
}

$dbh->disconnect();



